package edu.cnu.tpts.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.cnu.tpts.bean.Course;
import edu.cnu.tpts.bean.Student;
import edu.cnu.tpts.bean.StudentScore;
import edu.cnu.tpts.bean.Teacher;
import edu.cnu.tpts.dao.TeacherDao;
import edu.cnu.tpts.util.DBManager;

public class TeacherDaoImpl implements TeacherDao {

	/**
	 * 教师登录验证并获取教师信息
	 */
	public Teacher isTeacher(String name, String password) {
		DBManager dbManager = new DBManager();
		Teacher teacher = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("select * from teacher where loginname=? and password=? ");
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				teacher = new Teacher();
				teacher.setId(rs.getInt("id"));
				teacher.setLoginName(rs.getString("loginname"));
				teacher.setSex(rs.getString("sex"));
				teacher.setRealName(rs.getString("realname"));
				teacher.setTeaDepartment(rs.getString("tea_department"));
			}
			return teacher;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}

	}

	/**
	 * 按照教师登录姓名查询
	 * 
	 * @param loginName
	 * @param flag
	 *            ，判断注册的时候是否重名是设为false，此时不需要搜索课程（还没有课程呢），其他情况为true。
	 * @return
	 */
	public Teacher findByName(String loginName, boolean flag) {
		DBManager dbManager = new DBManager();
		Teacher teacher = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("select * from teacher where loginName=? ");
			pstmt.setString(1, loginName);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				teacher = new Teacher();
				teacher.setId(rs.getInt("id"));
				teacher.setLoginName(rs.getString("loginname"));
				teacher.setSex(rs.getString("sex"));
				teacher.setRealName(rs.getString("realname"));
				teacher.setTeaDepartment(rs.getString("tea_department"));
			}
			if (flag) {
				teacher.setCourse(this.getTeacheCourse(teacher)); // debug
			}
			return teacher;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}
	}

	// <<<<<<< .mine
	//
	// public List<StudentScore> findScores(int id) {
	// // TODO Auto-generated method stub
	// return null;
	// }
	// =======
	// >>>>>>> .r62
	/**
	 * 更新教师信息
	 */
	public boolean updateTeacher(Teacher teacher) {
		// TODO Auto-generated method stub
		DBManager dbManager = new DBManager();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("update  teacher set loginname=?,sex=?,tea_Department=?,password=?  where id=?");
			pstmt.setString(1, teacher.getLoginName());
			pstmt.setString(2, teacher.getSex());
			pstmt.setString(3, teacher.getTeaDepartment());
			pstmt.setString(4, teacher.getPassword());
			pstmt.setInt(5, teacher.getId());
			pstmt.execute();
			return true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}

	}

	/**
	 * 修改密码
	 */
	public boolean changePass(Teacher teacher) {
		// TODO Auto-generated method stub
		DBManager dbManager = new DBManager();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("update  teacher set password=?  where id=?");
			pstmt.setString(1, teacher.getPassword());
			pstmt.setInt(2, teacher.getId());
			pstmt.execute();
			return true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}
	}

	/**
	 * 查询所有教师
	 * 
	 */
	public List<Teacher> getAll() {
		// TODO Auto-generated method stub

		DBManager dbManager = new DBManager();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement("select * from teacher  ");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Teacher teacher = new Teacher();
				teacher.setId(rs.getInt("id"));
				teacher.setLoginName(rs.getString("loginname"));
				teacher.setSex(rs.getString("sex"));
				teacher.setRealName(rs.getString("realname"));
				teacher.setTeaDepartment(rs.getString("tea_department"));
				teacherList.add(teacher);
			}
			return teacherList;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}
	}

	public List<Teacher> findTeacher(String tname, String department,
			String course) {

		StringBuffer sbsql = new StringBuffer(
				"select * from teacher t,teacher_course tc,course c where t.id=tc.tea_id and c.id=tc.cou_id ");
		if (!(tname == null || tname.equals("")))
			sbsql.append(" and t.realname like '%" + tname + "%' ");
		if (!department.equals("all"))
			sbsql.append(" and t.tea_department like '%" + department + "%' ");
		if (course != null && !course.equals(""))
			sbsql.append(" and c.name= '" + course + "' ");
		//System.out.println(sbsql);
		DBManager dbManager = new DBManager();
		Connection conn = dbManager.getConnection();
		
		List<Teacher> tealist = new ArrayList<Teacher>();
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sbsql.toString());
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Teacher teacher = new Teacher();
				teacher.setId(rs.getInt(1));
				teacher.setRealName(rs.getString(2));
				teacher.setSex(rs.getString(5));
				teacher.setTeaDepartment(rs.getString(6));
				
				PreparedStatement pstmt2 = conn
						.prepareStatement("select * from course where id="
								+ rs.getInt(8));
			
				ResultSet rs2 = pstmt2.executeQuery();
				rs2.next();
				teacher.setCourseName(rs2.getString("name"));
			
				tealist.add(teacher);
				for (int i = 0; i < tealist.size(); i++) {
					Teacher teacher1 = (Teacher) tealist.get(i);
					List<Course> clist=teacher1.getCourse();
					Course course1=clist.get(0);
					System.out.println(course1.getId()+" "+course1.getName());
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tealist;
	}
	public static void main(String[] args) {
		TeacherDao teaDao = new TeacherDaoImpl();
		List tlist=teaDao.findTeacher(null, "all", null);
	
	}

	// /**
	// * ��ӽ�ʦ����ע�ᣩ
	// * @param student
	// */
	// public void addTeacher(Teacher teacher) {
	// DBManager dbManager = new DBManager();
	// Connection conn=dbManager.getConnection();
	// PreparedStatement pstmt=null;
	// ResultSet rs=null;
	// try {
	// pstmt=conn.prepareStatement("insert into
	// teacher(id,loginname,password,realname,sex,tea_department)
	// values(?,?,?,?,?,?)");
	// pstmt.setInt(1, teacher.getId());
	// pstmt.setString(2, teacher.getLoginName());
	// pstmt.setString(3, teacher.getPassword());
	// pstmt.setString(4, teacher.getRealName());
	// pstmt.setString(5, teacher.getSex());
	// pstmt.setString(6, teacher.getTeaDepartment());
	// pstmt.executeUpdate();
	//		  
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }finally{
	// dbManager.closeAll(rs, pstmt, conn);
	// }
	//		
	// }
	/**
	 * ���ձ�Ų�ѯ
	 */
	public Teacher findById(int id) {
		DBManager dbManager = new DBManager();
		Connection conn = dbManager.getConnection();
		Teacher teacher = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select * from teacher where id=?");
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				String loginname = rs.getString("loginname");
				String realname = rs.getString("realname");
				String sex = rs.getString("sex");
				String teaDepartment = rs.getString("tea_department");
				teacher = new Teacher();
				teacher.setId(id);
				teacher.setLoginName(loginname);
				teacher.setSex(sex);
				teacher.setRealName(realname);
				teacher.setTeaDepartment(teaDepartment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}
		return teacher;
	}

	// /**
	// * ���յ�¼���ѯ
	// */
	// public Teacher findByName(String name) {
	// DBManager dbManager = new DBManager();
	// Connection conn=dbManager.getConnection();
	// Teacher teacher=null;
	// PreparedStatement pstmt=null;
	// ResultSet rs=null;
	// try {
	// pstmt=conn.prepareStatement("select * from teacher where loginname=?");
	// pstmt.setString(1, name);
	// rs=pstmt.executeQuery();
	// if (rs.next()){
	// int id=rs.getInt("id");
	// String loginname=rs.getString("loginname");
	// String realname=rs.getString("realname");
	// String sex=rs.getString("sex");
	// String teaDepartment=rs.getString("tea_department");
	// teacher =new Teacher();
	// teacher.setId(id);
	// teacher.setLoginName(loginname);
	// teacher.setSex(sex);
	// teacher.setRealName(realname);
	// teacher.setTeaDepartment(teaDepartment);
	// }
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }finally{
	// dbManager.closeAll(rs, pstmt, conn);
	// }
	// return teacher;
	// }

	public List<Course> getTeacheCourse(Teacher teacher) {
		// TODO Auto-generated method stub

		DBManager dbManager = new DBManager();
		List<Course> courseList = new ArrayList<Course>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("select c.id,c.name from teacher t,course c,teacher_course tc where t.id=tc.tea_id and c.id=tc.cou_id and t.id=?  ");
			pstmt.setInt(1, teacher.getId());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Course course = new Course();
				course.setId(rs.getInt("id"));
				course.setName(rs.getString("name"));
				courseList.add(course);
			}
			return courseList;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}

	}

	/**
	 * 按课程类别查询某个教师的学生成绩
	 * 
	 * @param teacher
	 * @return
	 */
	public List<Student> findMyStudentScore(String courseStr) {
		// TODO Auto-generated method stub
		DBManager dbManager = new DBManager();
		List<Student> studentList = new ArrayList<Student>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		/*
		 * select s.*,c.id as cou_id,c.name as cou_name,ss.task_score,test_score
		 * from
		 * 
		 * student s inner join student_course sc on s.id=sc.stu_id
		 * 
		 * inner join course c on c.id=sc.cou_id left join student_score ss on
		 * ss.cou_id=c.id and ss.stu_id=s.id and c.id in(1,2);
		 */
		try {

			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("select s.*,c.id as cou_id,c.name as cou_name,ss.id as score_id,ss.task_score,test_score from  student s "
							+ " inner join  student_course sc  on  s.id=sc.stu_id "
							+ " inner join  course c on c.id=sc.cou_id and c.id in("
							+ courseStr
							+ ") "
							+ " left join student_score ss  on  ss.cou_id=c.id and ss.stu_id=s.id ");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Student student = new Student();
				student.setId(rs.getInt("id"));
				student.setRealName(rs.getString("realname"));
				student.setSex(rs.getString("sex"));
				student.setStuDepartment(rs.getString("stu_Department"));
				student.setStuClass(rs.getString("stu_Class"));
				// 附加属性
				student.setCouId(rs.getInt("cou_id"));
				student.setScoreId(rs.getInt("score_id"));
				student.setCourseName(rs.getString("cou_name"));
				student.setTaskScore(rs.getInt("task_score"));
				student.setTestScore(rs.getInt("test_score"));
				student.setTotalScore(rs.getInt("task_score")
						+ rs.getInt("test_score"));

				studentList.add(student);
			}
			return studentList;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}
	}

	/**
	 * 获得学生所学的课程
	 * 
	 * @param Teacher
	 *            teacher
	 * @return
	 */
	public List<Course> getStudentCourse(Student student) {
		// TODO Auto-generated method stub
		DBManager dbManager = new DBManager();
		List<Course> courseList = new ArrayList<Course>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("select c.id,c.name from student s,course c,student_course sc where s.id=sc.stu_id and c.id=sc.cou_id and s.id=?  ");
			pstmt.setInt(1, student.getId());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Course course = new Course();
				course.setId(rs.getInt("id"));
				course.setName(rs.getString("name"));
				courseList.add(course);
			}
			return courseList;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}
	}

	// <<<<<<< .mine
	// public static void main(String[] args) {
	// TeacherDao teaDao = new TeacherDaoImpl();
	// List<Teacher> tealist=teaDao.getAll();
	// for(Teacher stu:tealist){
	// System.out.println(stu.getId()+" "+stu.getRealName());
	// }
	// }
	// =======
	/**
	 * 查询某条成绩
	 * 
	 * @param scoreId
	 * @return
	 */
	public Student findOneScore(int stuId, int couId) {
		DBManager dbManager = new DBManager();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Student student = null;
		/*
		 * select s.*,c.id as cou_id,c.name as cou_name,ss.id as
		 * score_id,ss.task_score,test_score from student s inner join
		 * student_course sc on s.id=sc.stu_id inner join course c on
		 * c.id=sc.cou_id left join student_score ss on ss.cou_id=c.id and
		 * ss.stu_id=s.id and s.id=? and c.id=?
		 */

		try {
			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("select s.*,c.id as cou_id,c.name as cou_name,ss.id as score_id,ss.task_score,test_score from "
							+ "  student s  inner join  student_course sc  on  s.id=sc.stu_id "
							+ " inner join  course c on c.id=sc.cou_id   and s.id=? and c.id=? "
							+ " left join student_score ss  on  ss.cou_id=c.id and ss.stu_id=s.id   ");
			pstmt.setInt(1, stuId);
			pstmt.setInt(2, couId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				student = new Student();
				student.setId(rs.getInt("id"));
				student.setRealName(rs.getString("realname"));
				student.setSex(rs.getString("sex"));
				student.setStuDepartment(rs.getString("stu_Department"));
				student.setStuClass(rs.getString("stu_Class"));
				// 附加属性
				student.setCouId(rs.getInt("cou_id"));
				student.setScoreId(rs.getInt("score_id"));
				student.setCourseName(rs.getString("cou_name"));
				student.setTaskScore(rs.getInt("task_score"));
				student.setTestScore(rs.getInt("test_score"));
				student.setTotalScore(rs.getInt("task_score")
						+ rs.getInt("test_score"));

			}
			return student;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}
	}

	/**
	 * 查询某个学生某项课程的成绩
	 */
	public List<StudentScore> findScores(int couId) {
		DBManager dbManager = new DBManager();
		Connection conn = dbManager.getConnection();
		List<StudentScore> scorelist = new ArrayList<StudentScore>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn
					.prepareStatement("select s.*,c.name from student_score s,course c where  s.cou_id=c.id  and cou_id=? ");
			pstmt.setInt(1, couId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				StudentScore sscore = new StudentScore();
				sscore.setId(rs.getInt("id"));
				sscore.setCou_id(rs.getInt("cou_id"));
				sscore.setTaskScore(rs.getInt("task_score"));
				sscore.setTestScore(rs.getInt("test_score"));
				sscore.setTotalScore(rs.getInt("task_score")
						+ rs.getInt("test_score"));
				sscore.setStu_id(rs.getInt("stu_id"));
				scorelist.add(sscore);
			}

			return scorelist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}

	}

	/**
	 * 删除某条学生成绩
	 * 
	 * @param scoreId
	 * @return
	 */
	public boolean deleteStudentScore(int scoreId) {
		DBManager dbManager = new DBManager();
		Connection conn = dbManager.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn
					.prepareStatement("delete from student_score  where id=? ");
			pstmt.setInt(1, scoreId);
			pstmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}

	}

	/**
	 * 新增或修改某条学生成绩
	 * 
	 * @param scoreId
	 * @return
	 */
	public boolean updateStudentScore(StudentScore studentScore) {
		// TODO Auto-generated method stub
		DBManager dbManager = new DBManager();
		Connection conn = dbManager.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			if (0 == studentScore.getId()) {
				pstmt = conn
						.prepareStatement("insert into student_score(test_score,task_score,cou_id,stu_id) values( ? , ?, ?, ?) ");
				pstmt.setInt(1, studentScore.getTestScore());
				pstmt.setInt(2, studentScore.getTaskScore());
				pstmt.setInt(3, studentScore.getCou_id());
				pstmt.setInt(4, studentScore.getStu_id());
			} else {
				pstmt = conn
						.prepareStatement("update student_score  set test_score=? ,task_score=? where id=? ");
				pstmt.setInt(1, studentScore.getTestScore());
				pstmt.setInt(2, studentScore.getTaskScore());
				pstmt.setInt(3, studentScore.getId());
			}
			pstmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}

	}

	/**
	 * 添加教师
	 * 
	 * @param teacher
	 * @return
	 */
	public boolean addTeacher(Teacher teacher) {

		DBManager dbManager = new DBManager();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dbManager.getConnection();
			pstmt = conn
					.prepareStatement("insert into  teacher(id,realname,loginname,sex,tea_Department,password) values(?,?,?,?,?,?) ");
			pstmt.setInt(1, teacher.getId());
			pstmt.setString(2, teacher.getRealName());
			pstmt.setString(3, teacher.getLoginName());
			pstmt.setString(4, teacher.getSex());
			pstmt.setString(5, teacher.getTeaDepartment());
			pstmt.setString(6, teacher.getPassword());
			pstmt.execute();
			return true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}

	}

	public void deleteStudentCourse(int stuid, int couid) {
		DBManager dbManager = new DBManager();
		Connection conn = dbManager.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			pstmt = conn
					.prepareStatement("delete from student_score where stu_id="
							+ stuid + " and cou_id=" + couid);
			pstmt.execute();
			pstmt = conn
					.prepareStatement("delete from student_course where stu_id="
							+ stuid + " and cou_id=" + couid);
			pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbManager.closeAll(rs, pstmt, conn);
		}

	}

	// >>>>>>> .r62
}
